package com.example.covid19.mapper;

import com.example.covid19.common.Dto.QuanDto;
import com.example.covid19.entity.ChanRoom;
import com.example.covid19.entity.Room;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.covid19.entity.SelectClass.Room_count;
import com.example.covid19.entity.SelectClass.roomcmp;
import com.example.covid19.entity.Student;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author BaiZhengChun
 * @since 2023-03-07
 */
public interface RoomMapper extends BaseMapper<Room> {
    /**
     *
     * @param
     * @return符合性别并且排除掉当前居住的宿舍
     */
    @Select("SELECT " +
            " room.lid, " +
            " GROUP_CONCAT( room.rid ORDER BY room.rid ASC ) AS rids " +
            "FROM " +
            " builder " +
            " INNER JOIN room ON builder.lid = room.lid " +
            "WHERE " +
            " builder.sex = #{sex} " +
            " AND room.id <> #{rid} " +
            " AND room.isable = 1 " +
            "GROUP BY " +
            " room.lid;")
    List<ChanRoom> getroomlist(@Param("sex")Integer sex, @Param("rid")Integer rid);
    @Select("SELECT " +
            " room.rid, builder.build_name, count(*) as shuliang " +
            "FROM " +
            " room, student, builder " +
            "WHERE " +
            " room.rid = student.rid and builder.lid = room.lid and student.rid in(#{id}) and student.lid=builder.lid " +
            "GROUP BY room.rid")
    List<QuanDto>getquan(@Param("id")Integer id);
    @Select("SELECT COUNT(*) AS num_students, r.*, builder.build_name, builder.sex " +
            "FROM student AS s " +
            "RIGHT JOIN room AS r ON s.rid = r.id " +
            "LEFT JOIN builder ON r.lid = builder.lid " +
            "GROUP BY r.rid")
    List<Room_count> getroom_count();
    @Select("SELECT COUNT(*) AS num_students, r.*, builder.build_name, builder.sex " +
            "FROM student AS s " +
            "RIGHT JOIN room AS r ON s.rid = r.id " +
            "LEFT JOIN builder ON r.lid = builder.lid " +
            "GROUP BY r.rid" +
            " LIMIT #{pageNo},#{pagesize};")
    List<Room_count> getCount(@Param("pageNo")Integer pageNo,@Param("pagesize")Integer pagesize);
    /**
     * app端符合要求的寝室
     */
    @Select("SELECT " +
            " r.rid, " +
            " r.id, " +
            " r.lid, " +
            " r.isable, " +
            " r.typee, " +
            " r.specification, " +
            " COUNT( s.zid ) AS occupied " +
            "FROM " +
            " builder b " +
            " INNER JOIN room r ON b.lid = r.lid " +
            " AND r.id <> #{rid} " +
            " AND r.isable = 1 " +
            " AND r.typee = 1 " +
            " LEFT JOIN student s ON r.id = s.rid " +
            " WHERE " +
            " b.sex = #{sex} " +
            "GROUP BY " +
            " b.build_name, " +
            " r.rid " +
            " HAVING " +
            " occupied < r.specification")
    List<roomcmp> getcmproom(@Param("rid")Integer rid,@Param("sex")Integer sex);
    @Select("SELECT " +
            " r.rid, " +
            " r.id, " +
            " r.lid, " +
            " r.isable, " +
            " r.typee, " +
            " r.specification, " +
            " COUNT( s.zid ) AS occupied " +
            "FROM " +
            " builder b " +
            " INNER JOIN room r ON b.lid = r.lid " +
            " AND r.isable = 1 " +
            " AND r.typee = 1 " +
            " LEFT JOIN student s ON r.id = s.rid " +
            " WHERE " +
            " b.sex = #{sex} " +
            "GROUP BY " +
            " b.build_name, " +
            " r.rid " +
            " HAVING " +
            " occupied < r.specification")
    List<roomcmp> getcmproom2(@Param("sex")Integer sex);
    @Select("SELECT " +
            " builder.build_name, " +
            " COUNT(room.id) AS numStudents " +
            "FROM " +
            " builder " +
            " INNER JOIN " +
            " room " +
            " ON " +
            " builder.lid = room.lid " +
            "GROUP BY " +
            " builder.lid")
    List<Room_count> getzinfo();



}
